Amazon Redshift 負荷状況を把握するためのSQLレシピ

石川覚

2024.11.08

AWS事業本部コンサルティング部の石川です。よくわからないけど、負荷が高い、クエリが遅い、チューニングをしてコスト最適化したいなど負荷状況を把握したい人の理由は様々です。本日は、そんなときに役立つ負荷状況を把握する２つの方法をご紹介します。

頻繁に実行されているSQL一覧
頻繁に参照されているテーブル一覧

頻繁に実行されているSQL一覧

MySQLの利用者からよく聞かれる質問は、「slow queryはどうやって取得できますか？」です。Redshiftでは、システムテーブルからその情報を取得することが可能です。なので、もし、そのようなslow queryをファイルとして保存したい場合は、S3にファイルとしてUNLOADすることで同様の運用が可能です。

下記の例では、最も参照されているテーブルを出力しています。

基本

WITH query_text AS (
	SELECT query_id,
		LISTAGG(text) WITHIN GROUP (
			ORDER BY sequence
		) AS query_text
	FROM sys_query_text
	WHERE user_id >= 100
		AND sequence < 16
	GROUP BY query_id
),
query_all AS (
	select user_id,
		q.query_id,
		trim(u.query_text) as qrytext,
		md5(trim(u.query_text)) as qry_md5,
		q.start_time,
		q.end_time,
		q.elapsed_time / 1000 / 1000 as run_seconds
	FROM SYS_QUERY_HISTORY q
		INNER JOIN query_text u ON q.query_id = u.query_id
	WHERE user_id <> 1
		AND u.query_text NOT ILIKE '% pg_%'
		AND u.query_text NOT ILIKE '% svv_%'
		AND u.query_text NOT ILIKE '% sys_%'
		AND u.query_text NOT ILIKE 'select 1'
		AND u.query_text NOT ILIKE 'select current_user'
)
SELECT *
FROM query_all
ORDER BY run_seconds DESC;

返される結果には以下のような情報が含まれます。

user_id: dbユーザーID
query_id: クエリのID
qrytext: SQLテキスト
qry_md5: SQLテキストのmd5ハッシュ
start_time: クエリの開始時間（マイクロ秒）
end_time: クエリの終了時間（マイクロ秒）
run_seconds: クエリが実行した時間（秒）

実行例

下記の例では、最も参照されているテーブルの先頭10テーブル（LIMIT 10）を出力しています。

cmdb=# WITH query_text AS (
cmdb(#     SELECT query_id,
cmdb(#         LISTAGG(text) WITHIN GROUP (
cmdb(#             ORDER BY sequence
cmdb(#         ) AS query_text
cmdb(#     FROM sys_query_text
cmdb(#     WHERE user_id >= 100
cmdb(#         AND sequence < 16
cmdb(#     GROUP BY query_id
cmdb(# ),
cmdb-# query_all AS (
cmdb(#     select user_id,
cmdb(#         q.query_id,
cmdb(#         trim(u.query_text) as qrytext,
cmdb(#         md5(trim(u.query_text)) as qry_md5,
cmdb(#         q.start_time,
cmdb(#         q.end_time,
cmdb(#         q.elapsed_time / 1000 / 1000 as run_seconds
cmdb(#     FROM SYS_QUERY_HISTORY q
cmdb(#     WHERE user_id <> 1
cmdb(#         AND q.start_time > DATEADD(day, -1, CURRENT_DATE)
cmdb(#         AND u.query_text NOT ILIKE '% pg_%'
cmdb(#         AND u.query_text NOT ILIKE '% svv_%'
cmdb(#         AND u.query_text NOT ILIKE '% sys_%'
cmdb(#         AND u.query_text NOT ILIKE 'select 1'
cmdb(#         AND u.query_text NOT ILIKE 'select current_user'
cmdb(# )
cmdb-# SELECT *
cmdb-# FROM query_all
cmdb-# ORDER BY run_seconds DESC
cmdb-# LIMIT 10;
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
user_id     | 100
query_id    | 553760560
qrytext     | COPY "sales"."ia_trn_store_daily" *********************) FROM 's3://cm-datalake-20241108/************/ia_trn_store_daily.csv' credentials '' DELIMITER ',' CSV QUOTE '"' ENCODING UTF8 GZIP
qry_md5     | 2942df4b8f2dafbac148dc8af2428f28
start_time  | 2024-11-07 18:34:31.179948
end_time    | 2024-11-07 19:21:30.154076
run_seconds | 2818
-[ RECORD 2 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------
:
:

応用例1: 時間範囲の指定

指定した時間帯のテーブルを頻繁に参照しているかを特定します。下記の例では、最も参照されているテーブルを過去1日間分（AND starttime > DATEADD(day, -1, CURRENT_DATE)）出力しています。

WITH query_text AS (
	SELECT query_id,
		LISTAGG(text) WITHIN GROUP (
			ORDER BY sequence
		) AS query_text
	FROM sys_query_text
	WHERE user_id >= 100
		AND sequence < 16
		AND start_time > DATEADD(day, -1, CURRENT_DATE)
	GROUP BY query_id
),
query_all AS (
	select user_id,
		q.query_id,
		trim(u.query_text) as qrytext,
		md5(trim(u.query_text)) as qry_md5,
		q.start_time,
		q.end_time,
		q.elapsed_time / 1000 / 1000 as run_seconds
	FROM SYS_QUERY_HISTORY q
		INNER JOIN query_text u ON q.query_id = u.query_id
	WHERE user_id <> 1
		AND q.start_time > DATEADD(day, -1, CURRENT_DATE)
		AND u.query_text NOT ILIKE '% pg_%'
		AND u.query_text NOT ILIKE '% svv_%'
		AND u.query_text NOT ILIKE '% sys_%'
		AND u.query_text NOT ILIKE 'select 1'
		AND u.query_text NOT ILIKE 'select current_user'
)
SELECT *
FROM query_all
ORDER BY run_seconds DESC;

応用例2: ユーザー別の分析

どのユーザーがテーブルを頻繁に参照しているかを特定します。下記の例では、biuserユーザー（AND userid = (SELECT usesysid FROM pg_user WHERE usename = 'biuser')）が最も参照しているテーブルを出力しています。

WITH query_text AS (
	SELECT query_id,
		LISTAGG(text) WITHIN GROUP (
			ORDER BY sequence
		) AS query_text
	FROM sys_query_text
	WHERE user_id >= 100
		AND sequence < 16
    AND user_id = (SELECT usesysid FROM pg_user WHERE usename = 'biuser')
	GROUP BY query_id
),
query_all AS (
	select user_id,
		q.query_id,
		trim(u.query_text) as qrytext,
		md5(trim(u.query_text)) as qry_md5,
		q.start_time,
		q.end_time,
		q.elapsed_time / 1000 / 1000 as run_seconds
	FROM SYS_QUERY_HISTORY q
		INNER JOIN query_text u ON q.query_id = u.query_id
	WHERE user_id <> 1
		AND u.query_text NOT ILIKE '% pg_%'
		AND u.query_text NOT ILIKE '% svv_%'
		AND u.query_text NOT ILIKE '% sys_%'
		AND u.query_text NOT ILIKE 'select 1'
		AND u.query_text NOT ILIKE 'select current_user'
    AND q.user_id = (SELECT usesysid FROM pg_user WHERE usename = 'biuser')
)
SELECT *
FROM query_all
ORDER BY run_seconds DESC;

頻繁に参照されているテーブル一覧

Amazon Redshift で頻繁に参照されているテーブルのリストを取得するには、Amazon Redshift Utilities リポジトリにある AdminViews の v_get_top_queries ビューを流用しました。

下記の例では、最も参照されているテーブルを出力しています。

基本: v_get_tbl_scan_frequency

SELECT 
	database, 
	schema AS schemaname, 
	"table" AS tablename, 
	table_id, 
	size AS size_in_mb, 
	sortkey1, 
	NVL(s.num_qs,0) num_scans
FROM svv_table_info t
LEFT JOIN (SELECT
   tbl, perm_table_name,
   COUNT(DISTINCT query) num_qs
FROM
   stl_scan s
WHERE 
   s.userid > 1
GROUP BY 
   tbl, perm_table_name) s ON s.tbl = t.table_id
AND t."schema" !~ '^information_schema|catalog_history|pg_'
ORDER BY 7 desc;

返される結果には以下のような情報が含まれます。

database: テーブルが属するデータベース名
schema: テーブルのスキーマ名
table_name: テーブル名
size_in_mb: テーブルのサイズ（MB）
num_scans: テーブルがスキャンされた回数
num_rows: テーブル内の行数

実行例

下記の例では、最も参照されているテーブルの先頭10テーブル（LIMIT 10）を出力しています。

cmdb=# SELECT 
cmdb-# database, 
cmdb-# schema AS schemaname, 
cmdb-# table_id, 
cmdb-# "table" AS tablename, 
cmdb-# size, 
cmdb-# sortkey1, 
cmdb-# NVL(s.num_qs,0) num_qs
cmdb-# FROM svv_table_info t
cmdb-# LEFT JOIN (SELECT
cmdb(#    tbl, perm_table_name,
cmdb(#    COUNT(DISTINCT query) num_qs
cmdb(# FROM
cmdb(#    stl_scan s
cmdb(# WHERE 
cmdb(#    s.userid > 1
cmdb(#    AND s.perm_table_name NOT IN ('Internal Worktable','S3')
cmdb(# GROUP BY 
cmdb(#    tbl, perm_table_name) s ON s.tbl = t.table_id
cmdb-# AND t."schema" !~ '^information_schema|catalog_history|pg_'
cmdb-# ORDER BY 7 desc
cmdb-# LIMIT 10;
 database | schemaname | table_id |         tablename         |  size   |   sortkey1  | num_qs 
----------+------------+----------+---------------------------+---------+-------------+--------
 cmdb     | sales      |  6857063 | ia_mst_store              |    4960 | dpt_cd      |  27081
 cmdb     | sales      |  4468546 | ia_mst_aliance            |     912 | dpt_cd      |  21357
 cmdb     | sales      |  1948432 | ia_mst_store_group        |    3072 | grp_kbn     |  20765
 cmdb     | sales      |  2838751 | ia_mst_prt                |     544 | hkp_cd      |  20633
 cmdb     | sales      |  6957958 | ia_mst_store_summary      |     768 | sub_dpt_cd  |  18655
 cmdb     | sales      |  7543388 | ia_mst_store_summary_temp |    4960 | sub_dpt_cd  |  17358
 cmdb     | sales      |  2144083 | ia_mst_ope_item           |    9136 | item_cd     |  16757
 cmdb     | sales      |  3800801 | ia_mst_sub_item           |   22662 | item_cd     |  11463
 cmdb     | sales      |  3851923 | ia_trn_store_daily        |   10071 | create_date |  10726
 cmdb     | sales      |  5390770 | ia_mst_calendar           |     480 | cal_date    |   9690
(10 rows)

応用例1: 時間範囲の指定

指定した時間帯のテーブルを頻繁に参照しているかを特定します。下記の例では、最も参照されているテーブルを過去3日間分（AND starttime > DATEADD(day, -3, CURRENT_DATE)）出力しています。

SELECT 
	database, 
	schema AS schemaname, 
	"table" AS tablename, 
	table_id, 
	size AS size_in_mb, 
	sortkey1, 
	NVL(s.num_qs,0) num_scans
FROM svv_table_info t
LEFT JOIN (SELECT
   tbl, perm_table_name,
   COUNT(DISTINCT query) num_qs
FROM
   stl_scan s
WHERE 
   s.userid > 1
   AND s.perm_table_name NOT IN ('Internal Worktable','S3')
   AND starttime > DATEADD(day, -3, CURRENT_DATE)
GROUP BY 
   tbl, perm_table_name) s ON s.tbl = t.table_id
AND t."schema" !~ '^information_schema|catalog_history|pg_'
ORDER BY 7 desc;

応用例2: ユーザー別の分析

どのユーザーがテーブルを頻繁に参照しているかを特定します。下記の例では、biuserユーザー（AND s.userid = (SELECT usesysid FROM pg_user WHERE usename = 'biuser')）が最も参照しているテーブルを出力しています。

SELECT 
	database, 
	schema AS schemaname, 
	"table" AS tablename, 
	table_id, 
	size AS size_in_mb, 
	sortkey1, 
	NVL(s.num_qs,0) num_scans
FROM svv_table_info t
LEFT JOIN (SELECT
   tbl, perm_table_name,
   COUNT(DISTINCT query) num_qs
FROM
   stl_scan s
WHERE 
   s.userid > 1
   AND s.userid > (SELECT usesysid FROM pg_user WHERE usename = 'biuser')
   AND s.perm_table_name NOT IN ('Internal Worktable','S3')
GROUP BY 
   tbl, perm_table_name) s ON s.tbl = t.table_id
AND t."schema" !~ '^information_schema|catalog_history|pg_'
ORDER BY 7 desc;

最後に

Amazon Redshiftの負荷状況を把握するための2つの重要なSQLレシピが紹介しました。1つ目は頻繁に実行されているSQL一覧を取得する方法で、システムテーブルから情報を抽出し、クエリの実行時間や頻度を分析できます。2つ目は頻繁に参照されているテーブル一覧を取得する方法で、テーブルのスキャン回数やサイズなどの情報を得ることができます。

これらのレシピは、基本的な使用方法に加えて、時間範囲の指定やユーザー別の分析など、より詳細な分析のための応用例も提供しました。これらのSQLクエリを活用することで、Redshiftクラスターの性能問題を特定し、最適化の機会を見つけることができます。

データベース管理者やデータエンジニアにとって、これらのツールは日々の運用や長期的なパフォーマンス改善にお役立てできるのではないでしょうか。定期的にこれらの分析を行うことで、システムの健全性を維持し、ユーザーエクスペリエンスを向上に貢献できるはずです。また、これらのレシピは必要に応じてカスタマイズすることも可能で、各環境や要件に合わせて柔軟に対応できるはずです。